#! /bin/bash
#edu_ods_to_dwd_init.sh all/表名
#1、判断参数是否传入
if [ $# -lt 1 ]
then
	echo "必须传入表名/all参数..."
	exit
fi


dwd_trade_cart_add_inc_sql="set hive.execution.engine=mr;
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_cart_add_inc partition (dt)
select
    data.id,
    data.user_id,
    data.course_id,
    data.course_name,
    data.session_id,
    date_format(data.create_time,'yyyy-MM-dd') date_id,
    data.create_time,
    date_format(data.create_time,'yyyy-MM-dd')
from ods_cart_info_inc
where dt = '2022-06-14' and type = 'bootstrap-insert'; "
dwd_user_login_inc_sql="insert overwrite table dwd_user_login_inc partition(dt='2022-06-14')
select
    user_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd') date_id,
    date_format(from_utc_timestamp(ts,'GMT+8'),'yyyy-MM-dd HH:mm:ss') login_time,
    channel,
    province_id,
    version_code,
    mid_id,
    brand,
    model,
    operate_system
from
    (
        select
            user_id,
            channel,
            area_code,
            version_code,
            mid_id,
            brand,
            model,
            operate_system,
            ts
        from
            (
                select
                    user_id,
                    channel,
                    area_code,
                    version_code,
                    mid_id,
                    brand,
                    model,
                    operate_system,
                    ts,
                    row_number() over (partition by session_id order by ts) rn
                from
                    (
                        select
                            user_id,
                            channel,
                            area_code,
                            version_code,
                            mid_id,
                            brand,
                            model,
                            operate_system,
                            ts,
                            concat(mid_id,'-',last_value(session_start_point,true) over(partition by mid_id order by ts)) session_id
                        from
                            (
                                select
                                    common.uid user_id,
                                    common.ch channel,
                                    common.ar area_code,
                                    common.vc version_code,
                                    common.mid mid_id,
                                    common.ba brand,
                                    common.md model,
                                    common.os operate_system,
                                    ts,
                                    if(page.last_page_id is null,ts,null) session_start_point
                                from ods_log_inc
                                where dt='2022-06-14'
                                  and page is not null
                            )t1
                    )t2
                where user_id is not null
            )t3
        where rn=1
    )t4
        left join
    (
        select
            id province_id,
            substr(area_code,0,2) area_code
        from ods_base_province_full
        where dt='2022-06-14'
    )bp
    on t4.area_code=bp.area_code; "
dwd_traffic_page_view_inc_sql="set hive.cbo.enable=false;
insert overwrite table dwd_traffic_page_view_inc partition (dt='2022-06-14')
select
    t1.province_id,
    t1.brand,
    t1.channel,
    t1.is_new,
    t1.model,
    t1.mid_id,
    t1.operate_system,
    t1.source_id,
    t1.user_id,
    t1.version_code,
    t1.during_time,
    t1.page_item,
    t1.page_item_type,
    t1.last_page_id,
    t1.page_id,
    concat(mid_id,'-',last_value(t1.session_start_point,true) over (partition by t1.mid_id order by ts)) session_id,
    date_format(from_utc_timestamp(t1.ts,'GMT+8'),'yyyy-MM-dd') date_id
from (
         select common.ar province_id,
                common.ba brand,
                common.ch channel,
                common.is_new is_new,
                common.md model,
                common.mid mid_id,
                common.os operate_system,
                common.sc source_id,
                common.uid user_id,
                common.vc version_code,
                page.during_time during_time,
                page.item page_item,
                page.item_type page_item_type,
                page.last_page_id,
                page.page_id,
                if(page.last_page_id is null, ts, null) session_start_point,
                ts
         from ods_log_inc
         where dt = '2022-06-14' and page is not null
         )t1; "

dwd_trade_order_detail_inc_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_trade_order_detail_inc partition (dt)
select s1.id,
       s1.course_id,
       s1.course_name,
       s1.order_id,
       s1.user_id,
       s5.birthday,
       s1.origin_amount,
       s1.coupon_reduce,
       s1.final_amount,
       s1.session_id,
       s1.create_time,
       s1.update_time,
       s2.out_trade_no,
       s2.province_id,
       s6.name province_name,
       s3.subject_id,
       s4.subject_name,
       s3.publisher_id,
       s4.category_id,
       s7.category_name,
       s6.region_id,
       date_format(s1.create_time,'yyyy-MM-dd')
from (select data.id            id,
             data.course_id     course_id,
             data.course_name   course_name,
             data.order_id      order_id,
             data.user_id       user_id,
             data.origin_amount origin_amount,
             data.coupon_reduce coupon_reduce,
             data.final_amount  final_amount,
             data.session_id    session_id,
             data.create_time   create_time,
             data.update_time   update_time
      from ods_order_detail_inc
      where dt = '2022-06-14'
        and data.order_id is not null) s1
         left join
     (select data.id,
             data.out_trade_no out_trade_no,
             data.session_id   session_id,
             data.province_id  province_id
      from ods_order_info_inc
      where dt = '2022-06-14') s2 on
             s1.order_id = s2.id
         left join (
    select id,
           subject_id,
           publisher_id
    from ods_course_info_full
    where dt = '2022-06-14') s3 on s1.course_id = s3.id
         left join (select id, category_id, subject_name
                    from ods_base_subject_info_full
                    where dt = '2022-06-14') s4
                   on s3.subject_id = s4.id
         left join (
    select  id,
          birthday
    from dim_user_zip
    where dt = '9999-12-31'
) s5 on s1.user_id = s5.id
         left join (
    select id,
           name,
           region_id
    from ods_base_province_full
    where dt = '2022-06-14'
) s6 on s2.province_id = s6.id
         left join (
    select id,
           category_name
    from ods_base_category_info_full
    where dt = '2022-06-14'
) s7 on s4.category_id = s7.id;"

dwd_test_action_inc_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_test_action_inc partition(dt)
select
    paper_id,
    user_id,
    paper_score ,
    duration_sec,
    exam_id,
    question_id,
    is_correct,
    date_format(submit_time,'yyyy-MM-dd') date_id
from
(
    select
    data.paper_id,
    data.user_id,
    data.duration_sec,
    data.score paper_score,
    data.submit_time
    from ods_test_exam_inc
    where dt='2022-06-14'
     and type='insert'
)t1
join
(
    select
    data.user_id    user_id1  ,
    data.exam_id,
    data.question_id,
    data.is_correct
    from  ods_test_exam_question_inc
    where dt='2022-06-14'
     and type='bootstrap-insert'
)t2
on t1.user_id =t2.user_id1 ;"

dwd_course_evaluate_inc_sql="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_course_evaluate_inc partition (dt)
select
       data.id,
       data.user_id,
       data.course_id,
       data.review_txt,
       data.review_txt,
       data.review_stars,
       date_format(data.create_time, 'yyyy-MM-dd')
from ods_review_info_inc
where type = 'bootstrap-insert'and dt = '2022-06-14';"

dwd_user_register_inc_sql="set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table dwd_user_register_inc partition (dt)
select ui.user_id,
       date_format(create_time, 'yyyy-MM-dd') date_id,
       create_time,
       birthday,
       date_format(create_time, 'yyyy-MM-dd')
from (
         select data.id          user_id,
                data.create_time create_time,
                data.birthday    birthday
         from ods_user_info_inc
         where dt = '2022-06-14'
           and type = 'bootstrap-insert'
     ) ui;
	  "
dwd_videoplay_full_sql="with appvideo as  (
               select common.uid uid,appvideo.play_sec play_sec, appvideo.position_sec, appvideo.video_id video_id
               from ods_log_inc
               where appvideo.play_sec is not null and dt='2022-06-14'
           ) ,video_info as
           (
               select id, video_name, during_sec, chapter_id, course_id
               from ods_video_info_full
               where dt = '2022-06-14'
           )
,course as (
    select id,course_name,chapter_num
    from ods_course_info_full
    where dt = '2022-06-14'
),chapter as (
    select id,chapter_name,course_id
    from ods_chapter_info_full
    where dt = '2022-06-14'
)
insert overwrite table dwd_videoplay_full partition(dt='2022-06-14')
select

       distinct
        uid,
        t2.id,
        video_name,
        t2.course_id,
       course_name,
       t2.chapter_id,
        chapter_name,
        chapter_num,
       during_sec,
       play_sec,
       position_sec
from (
         select uid,
                t1.id,video_name,
                   during_sec,
                   chapter_id,
                   t1.course_id,
                   play_sec,
                   position_sec,
                   course_name,
                   chapter_num

         from (
                  select distinct uid,
                                  id,
                                  video_name,
                                  during_sec,
                                  chapter_id,
                                  course_id,
                                  play_sec,
                                  position_sec
                  from appvideo
                           left join video_info on appvideo.video_id = video_info.id
              ) t1
                  left join course on t1.course_id = course.id
     )t2
left join chapter on t2.chapter_id = chapter.id; "

dwd_payment_info_inc_sql=" set hive.exec.dynamic.partition.mode=nonstrict;
set hive.execution.engine=mr;
insert  overwrite table  dwd_payment_info_inc partition (dt)
select
	   s1.data.id id,
       s1.data.out_trade_no out_trade_no,
       s1.data.order_id order_id,
       s2.data.user_id user_id,
       s1.data.payment_type payment_type,
       s1.data.out_trade_no trade_no,
       s1.data.total_amount total_amount,
       s1.data.payment_status payment_status,
       s1.data.create_time create_time,
       s1.data.callback_time callback_time,
       s1.data.callback_content callback_content,
	   s2.data.origin_amount origin_amount,
	   s2.data.coupon_reduce coupon_reduce,
	   s2.data.final_amount final_amount,
	   s2.data.order_status order_status,
	   s2.data.trade_body trade_body,
	   s2.data.session_id session_id,
	   s2.data.province_id province_id,
		date_format(s1.dt, 'yyyy-MM-dd')
from  ods_payment_info_inc s1
left join ods_order_info_inc s2
on s1.data.order_id=s2.data.id
where s1.data.id is not null
and s1.dt='2022-06-14';"


dwd_user_chapter_process_inc_sql="set hive.exec.dynamic.partition.mode=nonstrict
set hive.execution.engine=mr;
insert overwrite table dwd_user_chapter_process_inc partition(dt)
select
    course_id,
    vif.chapter_id,
    user_id,
    position_sec,
    create_time,
    course_name,
    subject_id,
    update_time,
    during_sec,
    if(position_sec=during_sec,1,0) is_chapter_finish,
    date_format(dt,'yyyy-MM-dd')
from  (
    select distinct
    during_sec,
    chapter_id
from ods_video_info_full
where dt='2022-06-14'
    ) vif
right join (
    select
    data.user_id user_id,
    data.course_id course_id,
    data.position_sec position_sec,
    data.chapter_id chapter_id,
    data.create_time create_time,
    data.update_time update_time,
           dt
    from ods_user_chapter_process_inc
	where dt='2022-06-14'
	and type = 'bootstrap-insert'
    ) ucp
    join (
    select id,course_name,subject_id
    from dim_course_full
    ) t3
on vif.chapter_id=ucp.chapter_id
and ucp.course_id = t3.id;"




#2、根据第一个参数匹配导入数据到dwd
case $1 in
"all")

/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_trade_cart_add_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_user_login_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_traffic_page_view_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_test_action_inc_sql;"

/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_course_evaluate_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_user_register_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_videoplay_full_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_payment_info_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_user_chapter_process_inc_sql;"
/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_trade_order_detail_inc_sql;"
;;
"dwd_trade_cart_add_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_trade_cart_add_inc_sql;"
;;
"dwd_user_login_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_user_login_inc_sql;"
;;
"dwd_traffic_page_view_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_traffic_page_view_inc_sql;"
;;
"dwd_trade_order_detail_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_trade_order_detail_inc_sql;"

;;
"dwd_test_action_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_test_action_inc_sql;"

;;



"dwd_course_evaluate_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_course_evaluate_inc_sql;"

;;
"dwd_user_register_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;$dwd_user_register_inc_sql;"

;;
"dwd_videoplay_full")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;set hive.execution.engine=mr;$dwd_videoplay_full_sql;"

;;
"dwd_payment_info_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;set hive.execution.engine=mr;$dwd_payment_info_inc_sql;"

;;
"dwd_user_chapter_process_inc")
	/opt/module/hive/bin/hive -e "use edu;set hive.exec.dynamic.partition.mode=nonstrict;set hive.execution.engine=mr;$dwd_user_chapter_process_inc_sql;"

;;
*)
;;
esac